package com.example.wenhua.controller;

import com.example.wenhua.common.Result;
import com.example.wenhua.dto.AiQueryRequest;
import com.example.wenhua.dto.AiQueryResponse;
import com.example.wenhua.service.AiQueryService;
import com.example.wenhua.service.QueryExecutorService;
import com.example.wenhua.service.SqlGeneratorService;
import com.example.wenhua.utils.JwtUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import jakarta.servlet.http.HttpServletRequest;

/**
 * AI智能查询控制器
 */
@Slf4j
@RestController
@RequestMapping("/api/ai-query")
@CrossOrigin
public class AiQueryController {
    
    @Autowired
    private AiQueryService aiQueryService;
    
    @Autowired
    private SqlGeneratorService sqlGeneratorService;
    
    @Autowired
    private QueryExecutorService queryExecutorService;
    
    @Autowired
    private JwtUtil jwtUtil;
    
    /**
     * 智能查询接口
     */
    @PostMapping("/query")
    public Result<AiQueryResponse> intelligentQuery(@RequestBody AiQueryRequest request, HttpServletRequest httpRequest) {
        try {
            log.info("收到智能查询请求: {}", request.getQuery());
            
            // 获取用户信息
            String token = httpRequest.getHeader("Authorization");
            Integer userRole = 3; // 默认角色：学生
            String userId = "anonymous";
            
            if (token != null && token.startsWith("Bearer ")) {
                token = token.substring(7);
                try {
                    String username = jwtUtil.getUsernameFromToken(token);
                    userId = username;
                    // 这里可以根据用户名查询用户角色，暂时使用默认值
                    log.info("用户 {} 发起智能查询", username);
                } catch (Exception e) {
                    log.warn("解析token失败: {}", e.getMessage());
                }
            }
            
            // 1. 调用AI服务解析自然语言
            String aiResponse = aiQueryService.parseNaturalLanguageQuery(request.getQuery());
            
            if (aiResponse == null || aiResponse.trim().isEmpty()) {
                return Result.error("AI解析失败，请重新描述您的查询需求");
            }
            
            // 2. 提取SQL语句（假设AI返回的是纯SQL）
            String sql = extractSqlFromAiResponse(aiResponse);
            
            if (sql == null || sql.trim().isEmpty()) {
                return Result.error("无法从AI响应中提取有效的SQL语句");
            }
            
            // 3. 验证SQL安全性
            if (!sqlGeneratorService.validateSqlSafety(sql)) {
                return Result.error("查询包含不安全的操作，仅支持数据查询");
            }
            
            // 4. 清理和优化SQL
            sql = sqlGeneratorService.sanitizeSql(sql);
            
            // 5. 添加基于角色的权限限制
            sql = sqlGeneratorService.addRoleBasedRestrictions(sql, userRole, userId);
            
            // 6. 执行查询
            AiQueryResponse response = queryExecutorService.executeQueryWithResponse(
                sql, request.getQuery(), request.getNeedExplanation());
            
            // 7. 设置AI解析的意图
            response.setParsedIntent(extractIntentFromAiResponse(aiResponse));
            
            log.info("智能查询完成，返回 {} 条记录", response.getTotalCount());
            return Result.success(response);
            
        } catch (Exception e) {
            log.error("智能查询失败: {}", e.getMessage(), e);
            
            AiQueryResponse errorResponse = new AiQueryResponse();
            errorResponse.setSuccess(false);
            errorResponse.setErrorMessage(e.getMessage());
            
            return Result.success(errorResponse);
        }
    }
    
    /**
     * 验证SQL语句接口
     */
    @PostMapping("/validate-sql")
    public Result<Boolean> validateSql(@RequestBody String sql) {
        try {
            boolean isValid = sqlGeneratorService.validateSqlSafety(sql);
            return Result.success(isValid);
        } catch (Exception e) {
            log.error("SQL验证失败: {}", e.getMessage());
            return Result.error("SQL验证失败: " + e.getMessage());
        }
    }
    
    /**
     * 获取数据库结构信息接口
     */
    @GetMapping("/schema")
    public Result<String> getDatabaseSchema() {
        try {
            String schema = aiQueryService.buildDatabaseSchemaPrompt();
            return Result.success(schema);
        } catch (Exception e) {
            log.error("获取数据库结构失败: {}", e.getMessage());
            return Result.error("获取数据库结构失败: " + e.getMessage());
        }
    }
    
    /**
     * 从AI响应中提取SQL语句
     */
    private String extractSqlFromAiResponse(String aiResponse) {
        if (aiResponse == null) {
            log.warn("AI响应为null，无法提取SQL");
            return null;
        }
        
        log.info("开始从AI响应中提取SQL，响应内容: {}", aiResponse);
        
        // 查找SQL代码块
        String[] lines = aiResponse.split("\n");
        StringBuilder sql = new StringBuilder();
        boolean inSqlBlock = false;
        
        for (String line : lines) {
            String trimmedLine = line.trim();
            
            // 检测SQL代码块开始
            if (trimmedLine.startsWith("```sql") || trimmedLine.startsWith("```SQL")) {
                inSqlBlock = true;
                log.info("检测到SQL代码块开始");
                continue;
            }
            
            // 检测代码块结束
            if (trimmedLine.equals("```") && inSqlBlock) {
                log.info("检测到SQL代码块结束");
                break;
            }
            
            // 收集SQL语句
            if (inSqlBlock) {
                sql.append(line).append("\n");
            }
            
            // 如果没有代码块标记，但包含SELECT关键字，直接提取
            if (!inSqlBlock && trimmedLine.toUpperCase().startsWith("SELECT")) {
                sql.append(line).append("\n");
                log.info("检测到SELECT语句: {}", trimmedLine);
            }
        }
        
        String result = sql.toString().trim();
        log.info("初步提取的SQL: {}", result);
        
        // 如果没有找到SQL，尝试直接使用整个响应
        if (result.isEmpty() && aiResponse.toUpperCase().contains("SELECT")) {
            result = aiResponse.trim();
            log.info("使用整个响应作为SQL: {}", result);
        }
        
        if (result.isEmpty()) {
            log.warn("无法从AI响应中提取有效的SQL语句");
            return null;
        }
        
        log.info("最终提取的SQL: {}", result);
        return result;
    }
    
    /**
     * 从AI响应中提取查询意图
     */
    private String extractIntentFromAiResponse(String aiResponse) {
        if (aiResponse == null) {
            return "数据查询";
        }
        
        String upperResponse = aiResponse.toUpperCase();
        
        if (upperResponse.contains("统计") || upperResponse.contains("COUNT") || upperResponse.contains("SUM")) {
            return "统计分析";
        } else if (upperResponse.contains("查找") || upperResponse.contains("搜索") || upperResponse.contains("WHERE")) {
            return "条件查询";
        } else if (upperResponse.contains("排序") || upperResponse.contains("ORDER BY")) {
            return "排序查询";
        } else if (upperResponse.contains("分组") || upperResponse.contains("GROUP BY")) {
            return "分组查询";
        } else if (upperResponse.contains("关联") || upperResponse.contains("JOIN")) {
            return "关联查询";
        }
        
        return "数据查询";
    }
}